package com.dao;

import java.io.*;
import java.sql.*;

import com.bean.User;


/**
 * 对应users表的所有操作
 * @author hxd 
 */
public class UserSQL extends AbstractSQL{


	public  boolean storeUser(User user) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO users(name,password,email,sex,age,day) values(?,?,?,?,?,now())";// 
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			System.out.println("44444444444444444444444444444444");
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getSex());
			pstmt.setInt(5, user.getAge());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		try {//
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	public  User queryUser(String name) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT id, password,email,sex,age FROM users WHERE name=?";//
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int id=rs.getInt(1);
				String password = rs.getString(2);
				String email = rs.getString(3);
				String sex=rs.getString(4);
				int age=rs.getInt(5);
				pstmt.close();
				conn.close();
				return new User(id,name,password,email,sex,age);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public  void deleteUser(String name) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM users WHERE name= ?";// 
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public  User find(int id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select name,password,email,sex,age FROM users WHERE id= ?";// 
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				String name=rs.getString(1);
				String password = rs.getString(2);
				String email = rs.getString(3);
				String sex=rs.getString(4);
				int age=rs.getInt(5);
				pstmt.close();
				conn.close();
				return new User(id,name,password,email,sex,age);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public  boolean updateUser(User user){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update users set name=?,password=?,email=?,sex=?,age=? WHERE id= ?";// 
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getSex());
			pstmt.setInt(5, user.getAge());
			pstmt.setInt(6, user.getId());
			pstmt.executeUpdate();			
		} catch (SQLException e) {
			return false;
		}
		try {
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
		
	
	
//	public  void main(String[] args) {
//		      User user=queryUser("hxd");
//		      user.setPassword("dsfjl");
//		System.out.println(user.getName()+user.getPassword());
//	}
}
